package com.zbcx.generate.mbtiles.db;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zbcx.generate.mbtiles.ImageUtil;

/**
 * 添加数据到数据库
 * 
 * @author zbcx
 * 
 */
public class DBUtils {
	// 定义数据库连接参数
	public static final String DRIVER_CLASS_NAME = "org.sqlite.JDBC";

	public static final String URL = "jdbc:sqlite://f:/cx.mbtiles";

	private static ThreadLocal<Connection> connHolder = new ThreadLocal<Connection>();

	// 注册数据库驱动
	static {
		try {
			Class.forName(DRIVER_CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("注册失败！");
			e.printStackTrace();
		}
	}

	public static Connection getCurrentConnection() {
		Connection conn = connHolder.get();
		if (conn == null) {
			try {
				conn = getConn();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connHolder.set(conn);
		}
		return conn;
	}

	// 获取连接
	public static Connection getConn() throws SQLException {
		Connection conn = DriverManager.getConnection(URL);
		Statement stat = conn.createStatement();

		ResultSet rsTables = conn.getMetaData().getTables(null, null, "cx",
				null);
		if (rsTables.next()) {
			System.out.println("表存在,创建表的事情不要做了");
		} else {
			stat.executeUpdate("create table cx(zoom_level INTEGER,tile_row INTEGER,tile_column INTEGER,tile_data BLOB);");
		}
		return conn;
	}

	public static void closeConnection() {
		Connection conn = connHolder.get();
		if (conn != null) {
			closeConn(conn);
		}
	}

	// 关闭连接
	public static void closeConn(Connection conn) {
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("关闭连接失败！");
				e.printStackTrace();
			}
		}
	}

	/**
	 * 保存到数据库中
	 * 
	 * @param filePath
	 *            图片路径
	 * @param zoom_level
	 *            级数
	 * @param tile_row
	 *            行号
	 * @param tile_column
	 *            列号
	 */
	public static void insert(String filePath, Integer zoom_level,
			Integer tile_row, Integer tile_column) {
		Connection conn = null;
		PreparedStatement ps = null;

		File file = new File(filePath);
		try {
			conn = DBUtils.getCurrentConnection();

			String sql = "insert into cx(zoom_level, tile_row,tile_column,tile_data) values(?,?,?,?)";

			ps = conn.prepareStatement(sql);

			ps.setInt(1, zoom_level);
			ps.setInt(2, tile_row);
			ps.setInt(3, tile_column);
			ps.setBytes(4, ImageUtil.getImageByte(file));

			ps.execute();
			ps.close();

			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 保存到数据库中
	 * 
	 * @param file
	 *            图片文件
	 * @param zoom_level
	 *            级数
	 * @param tile_row
	 *            行号
	 * @param tile_column
	 *            列号
	 */
	public static void insert(File file, Integer zoom_level, Integer tile_row,
			Integer tile_column) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtils.getCurrentConnection();

			String sql = "insert into cx(zoom_level, tile_row,tile_column,tile_data) values(?,?,?,?)";

			ps = conn.prepareStatement(sql);

			ps.setInt(1, zoom_level);
			ps.setInt(2, tile_row);
			ps.setInt(3, tile_column);
			ps.setBytes(4, ImageUtil.getImageByte(file));

			ps.execute();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
}
